

IF OBJECT_ID('dbo.RptSubContractMaster') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractMaster
    IF OBJECT_ID('dbo.RptSubContractMaster') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractMaster >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractMaster >>>'
END  
GO


-- =======================================================================  
-- Procedure Name: dbo.RptSubContractMaster  
--  
-- Author:      Ponraja Chandran   
--  
-- Create date: 09/21/2009  
--  
-- Description: Gets Raw data of SubContract Master Details  
--  
-- Parameters:    
--     @DueDateFrom              
--     @DueDateTo     
--     @Institution   
--     @Department    
--     @Unit          
--     @FundNumber    
--     @PrincipalInvestigator
--     @Sponsor              
--     @OriginatingSponsor   
--     @Subcontractor        
--     @SubcontractSpecialist
--     @DeliverableType      
--     @DeliverableStatus    
--     @SelectedHeaderStatus
--     @GroupBy             
--     @DomainUserId        
--     @GroupByName                  
--  
-- Returns:     INT, 0=Success, -1=Error  
--  
-- SampleCall:  
-- EXEC RptSubContractMaster  '01/01/2007','01/31/2010','ALL','ALL','ALL','ALL','ALL', 'ALL','ALL','ALL','ALL','ALL','ALL',NULL,'ORG',11055
-- EXECUTE dbo.RptSubContractMaster  
--                       @DueDateFrom               = '6/1/2008',  
--                       @DueDateTo                 = '8/31/2009',  
--                       @Institution             = NULL,   
--                       @Department              = NULL,   
--                       @Unit                    = NULL,   
--					     @FundNumber              = NULL,  
--						 @PrincipalInvestigator   = NULL,   
--						 @Sponsor                 = NULL,  
--						 @OriginatingSponsor      = NULL,  
--                       @Subcontractor           = NULL,   
--                       @SubcontractSpecialist   = NULL,  
--						 @DeliverableType         = NULL,  
--                       @DeliverableStatus       = NULL,
--						 @SelectedHeaderStatus   = NULL,   
--						 @ContractManager	= NULL,   
--					     @GroupBy   ='CM',          
--					     @DomainUserId   = 11055  
--  
--------------------------------------------------------------------------  
-- Date        Initials  Modification  
--------------------------------------------------------------------------  
-- 09/21/09    PC        Created based on SQL Script provide by Ryan
-- =======================================================================  
  


CREATE PROCEDURE dbo.RptSubContractMaster(  
     @DueDateFrom                        DATETIME,    
     @DueDateTo                           DATETIME,  
     @Institution                       VARCHAR(100) = NULL,  
     @Department                        VARCHAR(100) = NULL,  
     @Unit                              VARCHAR(100) = NULL,  
     @FundNumber                        VARCHAR(1000) = NULL,  
     @PrincipalInvestigator             VARCHAR(100) = NULL,    
     @Sponsor                           VARCHAR(100) = NULL,  
     @OriginatingSponsor                VARCHAR(100) = NULL,  
     @Subcontractor                     VARCHAR(100) = NULL,  
     @SubcontractSpecialist             VARCHAR(100) = NULL,  
     @DeliverableType                   VARCHAR(100) = NULL,  
     @DeliverableStatus					VARCHAR(5000) = NULL, 
     @SelectedHeaderStatus				VARCHAR(1000) = NULL,   
     @ContractManager					VARCHAR(100) = NULL,
     @GroupBy                           VARCHAR(20) = NULL,  
     @DomainUserId                      INT     )   
WITH RECOMPILE                                            
AS  
BEGIN  
     -- Initial Settings    
     SET NOCOUNT ON    
     SET ANSI_WARNINGS OFF    
         
     -- Declare Local Variables    
     DECLARE   @Error              INT,    
               @RowCount           INT,    
               @ReturnCode         INT    
         
     DECLARE   @SubcontractsMaster TABLE (    
           
        Institution              VARCHAR(50)    NULL,    
        InstitutionId            INT            NULL,  
        ProposalNumber    VARCHAR(50)    NULL,    
        PSFundNumber    VARCHAR(50)    NULL,  
        PIName      VARCHAR(100)    NULL,   
        ChiefCode                VARCHAR(4)     NULL,   
        InstrumentType           VARCHAR(50)    NULL,    
        Sponsor      VARCHAR(150)    NULL,    
        SponsorId			VARCHAR(50)           NULL,    
        SubDeliverableName   VARCHAR(200)    NULL,  
        SubCategory     VARCHAR(200)   NULL,  
        PostAwardManager   VARCHAR(75) NULL,  
        RMStaffMember    VARCHAR(75) NULL,  
        DeliverableDueDate   DATETIME  NULL,   
        ProjectStart    DATETIME  NULL,   
        ProjectEnd     DATETIME  NULL,   
        SubInDeliverableStatus   VARCHAR(100)   NULL,     
        SubInDeliverableComments VARCHAR(2000)  NULL,            
        Department VARCHAR(50) NULL,  
        DepartmentId INT NULL,  
        SubContractorId  VARCHAR(50) NULL,  
        SubContractorName VARCHAR(150),  
        Originating_Sponsor VARCHAR(150),  
		ContractSpecialistID	 VARCHAR(100)   NULL,
		ContractSpecialist		 VARCHAR(60)	NULL,
        PAST30DAYS     INT   NULL,      
		PAST60DAYS     INT   NULL,      
		PAST90DAYS     INT   NULL, 
		PAST90PDAYS     INT   NULL,      
		FUT30DAYS     INT   NULL,      
		FUT60DAYS     INT   NULL,      
		FUT90DAYS     INT   NULL,
		FUT90PDAYS     INT   NULL 
        )  
                   
    
    
     -- Let Detail Proc do the work.    
     INSERT    @SubcontractsMaster    
                    (Institution,  
                     InstitutionId,  
     ProposalNumber,  
     PSFundNumber,  
     PIName,  
     ChiefCode,   
     InstrumentType,  
     Sponsor,   
      SponsorID,   
     SubDeliverableName,  
     SubCategory,  
     PostAwardManager,   
     RMStaffMember,   
     DeliverableDueDate,   
     ProjectStart,   
     ProjectEnd,   
     SubInDeliverableStatus,    
     SubInDeliverableComments,       
     Department,  
     DepartmentId,  
     SubContractorId,  
     SubContractorName,  
     Originating_Sponsor,  
	 ContractSpecialistID,
	 ContractSpecialist, 
     PAST30DAYS,      
     PAST60DAYS,      
     PAST90DAYS,   
	 PAST90PDAYS,      
     FUT30DAYS,      
     FUT60DAYS,      
     FUT90DAYS,
     FUT90PDAYS  
 )    
     EXECUTE @ReturnCode = dbo.RptSubContractMasterDetails  
       @DueDateFrom  = @DueDateFrom,  
       @DueDateTo = @DueDateTo,  
       @Institution=@Institution,  
       @Department = @Department,  
       @Unit = @Unit ,  
       @FundNumber = @FundNumber,  
       @PrincipalInvestigator = @PrincipalInvestigator,    
       @Sponsor =@Sponsor,  
       @OriginatingSponsor = @OriginatingSponsor,  
       @Subcontractor = @Subcontractor,  
       @SubcontractSpecialist = @SubcontractSpecialist,  
       @DeliverableType =@DeliverableType,  
       @DeliverableStatus = @DeliverableStatus, 
       @SelectedHeaderStatus = @SelectedHeaderStatus,
	   @ContractManager			= @ContractManager,
       @GroupBy =@GroupBy,  
       @DomainUserId =@DomainUserId    
                                  

     -- check for errors    
     SELECT @Error=@@ERROR    
     IF @Error != 0 OR @ReturnCode != 0    
     BEGIN    
          RETURN -1    
     END    
    
--     -- We have to return some search parameter names so they can display in the headers.    
--     -- Only if searching on one and only one value    
--     IF @Institution IS NOT NULL AND CHARINDEX(',',@Institution) = 0    
--          SELECT    TOP 1 @Institution = Institution    
--          FROM      @SubcontractsMaster    
--     ELSE    
--          SELECT    @Institution = NULL    
--         
--     IF @Department IS NOT NULL AND CHARINDEX(',',@Department) = 0    
--          SELECT    TOP 1 @Department =Department    
--          FROM      @SubcontractsMaster    
--     ELSE    
--          SELECT    @Department = NULL    
--    
--     IF @Unit IS NOT NULL AND CHARINDEX(',',@Unit) = 0    
--          SELECT    TOP 1 @Unit = Unit    
--          FROM      @SubcontractsMaster    
--     ELSE    
--          SELECT    @Unit = NULL    
--    
--     IF @PrincipalInvestigator IS NOT NULL AND CHARINDEX(',',@PrincipalInvestigator) = 0    
--          SELECT    TOP 1 @PrincipalInvestigator = PrincipalInvestigator    
--          FROM      @SubcontractsMaster    
--     ELSE    
--          SELECT    @PrincipalInvestigator = NULL    
--         
--     IF @OriginatingSponsor IS NOT NULL AND CHARINDEX(',',@OriginatingSponsor) = 0    
--          SELECT    TOP 1 @OriginatingSponsor = SecondarySponsor    
--          FROM      @SubcontractsMaster    
--     ELSE    
--          SELECT    @OriginatingSponsor = NULL    
--    
--     IF @Sponsor IS NOT NULL AND CHARINDEX(',',@Sponsor) = 0    
--          SELECT    TOP 1 @Sponsor = SponsorName    
--          FROM      @SubcontractsMaster    
--     ELSE    
--          SELECT    @Sponsor = NULL    
    
      --Return Result Set    
     SELECT    @DeliverableStatus As DeliverableStatus,    
               @Institution AS Institution,    
               @Department AS Department,    
               @Unit AS Unit,    
               @PrincipalInvestigator AS PrincipalInvestigator,    
               @OriginatingSponsor AS OriginatingSponsor,    
               @Sponsor AS Sponsor,    
			    SUM(PAST30DAYS) AS PAST30DAYS,      
			  SUM(PAST60DAYS) AS PAST60DAYS,      
			  SUM(PAST90DAYS) AS PAST90DAYS, 
			  SUM(PAST90PDAYS) AS PAST90PDAYS,      
			  SUM(FUT30DAYS) AS FUT30DAYS,      
			  SUM(FUT60DAYS) AS FUT60DAYS,      
			  SUM(FUT90DAYS) AS FUT90DAYS,
			  SUM(FUT90PDAYS) AS FUT90PDAYS,  
               GroupBy,    
               GroupById1,    
               GroupByName1,    
               --GroupById2,  
               GroupByName2,  
               GroupByName3
     FROM      (    
               SELECT    @GroupBy AS GroupBy,    
                         CASE @GroupBy     
                         WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     -- -1 means not set    
                              ELSE NULL    
                         END AS GroupById1,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')    
                              ELSE NULL    
                         END AS GroupByName1,    
--                         CASE @GroupBy     
--                              WHEN 'ORG' THEN ISNULL(InstrumentType,-1)  -- -1 means not set  
--							  --WHEN 'CM' THEN ISNULL(ContractSpecialistID,-1)   -- -1 means not set  
--                              ELSE -1    
--                         END AS GroupById2,          
                         CASE @GroupBy     
                              --WHEN 'ORG' THEN ISNULL(ChiefCode + ' - ' + Department,'UNASSIGNED')    
							  WHEN 'ORG' THEN ISNULL(InstrumentType,-1)    
                              WHEN 'SC' THEN ISNULL(SubContractorName,'UNASSIGNED')  
							  WHEN 'CM' THEN ISNULL(ContractSpecialist,'UNASSIGNED')  
                              ELSE ISNULL(RMStaffMember,'UNASSIGNED')    
                         END AS GroupByName2,  
						 CASE @GroupBy 
                              --WHEN 'ORG' THEN ISNULL(ChiefCode,'0') 
							  WHEN 'CM' THEN ISNULL(ContractSpecialistID,'UNASSIGNED') -- -1 means not set							  
                              ELSE '0'
                         END AS GroupByName3,                         
                          SUM(PAST30DAYS) AS PAST30DAYS,      
						  SUM(PAST60DAYS) AS PAST60DAYS,      
						  SUM(PAST90DAYS) AS PAST90DAYS, 
						  SUM(PAST90PDAYS) AS PAST90PDAYS,      
						  SUM(FUT30DAYS) AS FUT30DAYS,      
						  SUM(FUT60DAYS) AS FUT60DAYS,      
						  SUM(FUT90DAYS) AS FUT90DAYS,
						  SUM(FUT90PDAYS) AS FUT90PDAYS 
                           
               FROM      @SubcontractsMaster    
               GROUP BY  
						 CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)         
                              ELSE NULL    
                         END,    
                         CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')    
                              ELSE NULL    
                         END,    
--                         CASE @GroupBy     
--                              WHEN 'ORG' THEN ISNULL(InstrumentType,-1)  -- -1 means not set    
--							  WHEN 'CM' THEN ISNULL(ContractSpecialistID,-1)   -- -1 means not set
--                              ELSE -1    
--                         END,    
                         CASE @GroupBy     
                              --WHEN 'ORG' THEN ISNULL(ChiefCode + ' - ' + Department,'UNASSIGNED')    
                              WHEN 'ORG' THEN ISNULL(InstrumentType,-1)   
                              WHEN 'SC' THEN ISNULL(SubContractorName,'UNASSIGNED')    
							  WHEN 'CM' THEN ISNULL(ContractSpecialist,'UNASSIGNED')
                              ELSE ISNULL(RMStaffMember,'UNASSIGNED')     
                         END, 
						 CASE @GroupBy 
                              --WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set
							  WHEN 'CM' THEN ISNULL(ContractSpecialistID,'UNASSIGNED') 							  
                              ELSE '0'
                         END
 ) DC    
     GROUP BY     
               GroupBy,    
               GroupById1,    
               GroupByName1,    
               --GroupById2,    
               GroupByName2,
			   GroupByName3
     ORDER BY  GroupBy,    
               GroupByName1,    
               GroupByName2    
                   
     -- check for errors    
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT    
     IF @Error != 0     
     BEGIN    
          RETURN -1    
     END 
     -- Return Success    
     RETURN 0    
END    
GO

IF OBJECT_ID('dbo.RptSubContractMaster') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractMaster >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractMaster >>>'
GO
